package com.friday.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.friday.dao.WorkDayDao;
import com.friday.po.Department;
import com.friday.po.WorkDay;
import com.friday.util.DBUtil;

public class WorkDayDaoImpl implements WorkDayDao {
	private Connection conn = null;
	private Statement stmt = null;
	private PreparedStatement pStmt = null;
	private ResultSet rs = null;
	private DBUtil util = null;
	
	@Override
	public int insertWorkDay(WorkDay wd) throws Exception {
		String sql = "insert into workday values(?, ?, ?)";
		int ret;
		
		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			conn.setAutoCommit(false);
			pStmt = conn.prepareStatement(sql);
			pStmt.setString(1, wd.getId());
			pStmt.setDate(2, wd.getDate());
			pStmt.setString(3, wd.getState());
			ret = pStmt.executeUpdate();
			conn.commit();
		} catch (Exception e) {
			conn.rollback();
			throw e;
		} finally {
			util.close(pStmt);
			util.close(conn);
		}
		
		return ret;
	}

	@Override
	public int deleteWorkDay(String id) throws Exception {
		String sql = "delete from workday where id = ?";
		int ret = 0;
		
		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			conn.setAutoCommit(false);
			pStmt = conn.prepareStatement(sql);
			pStmt.setString(1, id);
			ret = pStmt.executeUpdate();
			conn.commit();
		} catch (Exception e) {
			conn.rollback();
			throw e;
		} finally {
			util.close(pStmt);
			util.close(conn);
		}
		
		return ret;
	}

	@Override
	public int updateWorkDay(WorkDay wd) throws Exception {
		String sql = "update workday set state = ? where id = ?";
		int ret;
		
		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			conn.setAutoCommit(false);
			pStmt = conn.prepareStatement(sql);
			pStmt.setString(1, wd.getState());
			pStmt.setString(2, wd.getId());
			ret = pStmt.executeUpdate();
			conn.commit();
		} catch (Exception e) {
			conn.rollback();
			throw e;
		} finally {
			util.close(pStmt);
			util.close(conn);
		}
		
		return ret;
	}

	@Override
	public List<WorkDay> queryDaysBetween(Date begin, Date end) throws Exception {
		String sql = "select * from workday where day between ? and ? order by day";
		List<WorkDay> list = new ArrayList<WorkDay>();
		
		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			pStmt = conn.prepareStatement(sql);
			pStmt.setDate(1, begin);
			pStmt.setDate(2, end);
			rs = pStmt.executeQuery();
			while(rs.next()){
				String id = rs.getString("id");
				Date date = rs.getDate("mday");
				String state = rs.getString("state");
 				WorkDay wd = new WorkDay(id, date, state);
				list.add(wd);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			util.close(rs);
			util.close(stmt);
			util.close(conn);
		}
		
		return list;
	}

	@Override
	public WorkDay queryDayByDate(Date date) throws Exception {
		String sql = "select * from workday where day = ?";
		WorkDay workDay = null;
		
		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			pStmt = conn.prepareStatement(sql);
			pStmt.setDate(1, date);
			rs = pStmt.executeQuery();
			while(rs.next()){
				String id = rs.getString("id");
				String state = rs.getString("state");
 				workDay = new WorkDay(id, date, state);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			util.close(rs);
			util.close(stmt);
			util.close(conn);
		}
		
		return workDay;
	}

}